#!/bin/bash
# Updated: 10/08/2006 by Brad House (brad <at> monetra <dot> com)
# Updated: 5/04/2007 by Ethan Burnside & Brian Barrett at the 
#     National Cristina Foundation (burnside <at> cristina <dot> org)
#     Added MySQL support.
# Updated: 1/2/2011 by Andrew Nagel (andrew.nagel <at> gmail <dot> com)
#	  Reworked the script to be used by the Skype program, removed the 
#	  old response method and reworked the existing lookup methods where
#	  possible added whitepages.com.  Dropped Google as it is no longer
#	  functioning.  PhoneNumber.com and AnyWho could probably be integrated
#	  with a bit of work but they seem to provide similar results to 411 and 
#	  whitepages.com so I didn't bother.
#
# ./callerid_shell.agi "phonenum" "lookup order" "manual name"
#
# Requires: CURL (http://curl.haxx.se/)
#           SQLite3 [Only required if using database cache] 
#                   (http://www.sqlite.org)
#           MySQL [Only required if using database cache] 
#                   (http://www.mysql.org)
#
# MySQL Table Structure
#
# CREATE TABLE `callerid` (
#  `id` int(11) NOT NULL auto_increment,
#  `areacode` int(11) NOT NULL,
#  `phonenum` int(11) NOT NULL,
#  `calleridname` varchar(50) NOT NULL,
#  `origin` varchar(50) NOT NULL,
#  `ts` bigint(20) NOT NULL,
#  `perm` tinyint(4) NOT NULL,
#  PRIMARY KEY  (`id`),
#  KEY `area-phone` (`areacode`,`phonenum`)
# );
#
# Additional MySQL Feature - set "perm" column to 1 to keep entry from
#    being purged.  
#
# Lookup order may contain a space separated list of:
#    411        - 411.com lookup (moderate speed)
#    whitepages - WhitePages.com lookup (moderate speed)
#    sqlite3    - Cached Database
#    mysql      - Cached Database
#    nanpa      - look up from database of NPA-NXX codes to find
#		city and state of caller, can obtain latest
#		list from: http://www.nanpa.com/reports/reports_cocodes_assign.html
#		Just unzip the 'all states' version and point the nanpa_db
#		variable below...
#  You may also specify the special 'manual' key and the
#  3rd arg 'manual name' in order to simply insert a static
#  entry into the database cache.
#
# Example:
#    ./callerid_shell.agi 555-555-1212 "manual" "John Doe"
#        - Will add entry into database cache as non-expiring
#          for phone number 555 5551212 ...
#    ./callerid_shell.agi 555-555-1212
#        - Will dump name to stdout

# --------- SET THESE VARIABLES ---------

# Order to follow to lookup callerid
#  if using cache, sqlite3 should always be first,
#  nanpa is typically last.  Space separated list.
#  (warning, anywho is slow, recommended not to use them)
lookup_order="mysql 411 whitepages nanpa"

# Whether or not we want to auto-cache successful lookups
#
# 0 = off
# 1 = sqllite
# 2 = mysql
#
cache_lookups=2

# Whether or not to cache nanpa lookups 
# (if enabled, must also have cache_lookups enabled)
cache_nanpa=1

#1/2/2011: A. Nagel - Does nothing at the moment, clean code needs to be fixed
# Expiration timeframe in seconds for cached lookups
cache_expire=7776000  # 90 days

# SQLite database location (full path)
sqlite3db="/var/lib/asterisk/agi-bin/callerid_sqlite3.db"  

# SQLite command
sqlitecmd="sqlite3" 

#
# MySQL Variables
#
mysqlcmd="/usr/bin/mysql"
mysqlhost="localhost"
mysqluser="cid"
mysqlpass="cid"
mysqldb="cid"
mysqltable="callerid"

# Location of downloaded NANPA db/txt ...
#  (download all states list from:
#    http://www.nanpa.com/reports/reports_cocodes_assign.html
#   and uncompress)
nanpa_db="/var/data/allutlzd.txt"


# --------------------- SCRIPT BELOW ------------------------

replace_escaped_chars() {
	data="$*"
	data=`echo ${data} | sed -e 's/+/ /g'`
	data=`echo ${data} | sed -e 's/%20/ /g'`
	data=`echo ${data} | sed -e 's/%26/\&/g'`
	echo "${data}"
}

get_rawnumber() {
	mynum="${1}"
	mynum=`echo "${mynum}" | sed -e 's/+1//g' | sed -e 's/ //g' | sed -e 's/-//g' | sed -e 's/(//g' | sed -e 's/)//g'`
	firstnum=`echo "${mynum}" | head -c 1 -`
	if [ "${firstnum}" = "1" ] ; then
		mynum=`echo "${mynum}" | sed -e 's/1//'`
	fi
	echo "${mynum}"
}

get_areacode() {
	mynum=`get_rawnumber "${1}" | head -c 3 -`
	echo "${mynum}"
}

get_phonenum() {
	areacode=`get_areacode "${1}"`
	mynum=`get_rawnumber "${1}" | sed -e "s/${areacode}//"`
	echo "${mynum}"
}

get_nxx() {
	mynum=`get_phonenum "${1}" | head -c 3 -`
	echo "${mynum}"
}

lookup_411() {
	myname=""

	myname=`/usr/bin/curl -s -m 2 -A Mozilla/4.0 http://www.411.com/search/ReversePhone?phone=${1} | grep -m 2 a\ href=\"/search/Replay? | tail -1 | sed -e 's:^[ \t]*::;s:[ \t]*$::' | sed -e 's:<a[^>]*>\([^<]*\)</a>:\1:'`
	
	if [ "${myname}" != "" ] ; then
		echo "${myname}"
	fi
}

#removed 1/2/2011: A. Nagel
#lookup_google() {
#	data=""
#	myname=""
#
#	data=`/usr/bin/curl -s -m 2 -A Mozilla/4.0 http://www.google.com/search?q=phonebook:${1}`
#	myname=`echo ${data} | grep Results | sed -e 's/.*Results//g' | sed -e 's:.*<font size=-2><br></font><font size=-1>::g' | sed -e 's:<.*::g' | cut -d- -f1`
#	if [ "${myname}" = "" -o "${myname}" = " " ] ; then
#		# Layout change, let's try catching this one.
#		myname=`echo ${data} | grep Results | sed -e 's/.*Results//g' | sed -e 's:.*<font size=-2><br></font>::g' | sed -e 's:.*9><td>::' | sed -e 's:<.*::g' | cut -d- -f1`
#	fi
#
#	if [ "${myname}" != "" ] ; then
#		echo "${myname}"
#	fi
#}

lookup_whitepages() {
	myname=""

	myname=`/usr/bin/curl -s -m 2 -A Mozilla/4.0 http://www.whitepages.com/search/ReversePhone?full_phone=${1} | grep -m 1 class=\"name\" | sed -e 's:^[ \t]*::;s:[ \t]*$::' | sed -e 's:<span[^>]*><a[^>]*>\([^<]*\)<\/a><\/span>:\1:'`
	
	if [ "${myname}" != "" ] ; then
		echo "${myname}"
	fi
}

#added 1/2/2011: A. Nagel - doesn't work kept around as a base for future improvement
#lookup_phonenumber() {
#	data=""
#	myname=""
#
#	myname=`/usr/bin/curl -s -m 2 -A Mozilla/4.0 http://www.phonenumber.com/search/ReversePhone?phone=${1} | grep -m 1 <h3> | sed -e 's:^[ \t]*::;s:[ \t]*$::' | sed -e 's:<h3><a[^>]*>\([^<]*\)<\/a><\/h3>:\1:'`
#	
#	if [ "${myname}" != "" ] ; then
#		echo "${myname}"
#	fi
#}

#removed 1/2/2011 A. Nagel
#lookup_anywho() {
#	data=""
#	fname=""
#	lname=""
#	myname=""
#
#	areacode=`get_areacode ${1}`
#	phonenum=`get_phonenum ${1}`
#	url="http://www.anywho.com/qry/wp_rl?npa=${areacode}&telephone=${phonenum}&btnsubmit=Search"
#	data=`/usr/bin/curl -s -m 2 -A Mozilla/4.0 "$url" | grep 'urlgen.rmservers.com'`
#	fname=`echo ${data} | sed -e 's/.*firstname=//g' | cut -d\& -f1`
#	lname=`echo ${data} | sed -e 's/.*lastname=//g' | cut -d\& -f1`
#	if [ "${fname}" != "" -a "${lname}" != "" ] ; then
#		fname=`replace_escaped_chars ${fname}`
#		lname=`replace_escaped_chars ${lname}`
#		myname="${lname}, ${fname}"
#	fi
#
#	if [ "${myname}" != "" ] ; then
#		echo "${myname}"
#	fi
#}

lookup_sqlite3() {
	areacode=`get_areacode ${1}`
	phonenum=`get_phonenum ${1}`

	if [ "${areacode}" = "" -o "${phonenum}" = "" ] ; then
		return 1
	fi

	if [ ! -f ${sqlite3db} ] ; then
		return 1
	fi

	curr_ts=`date +%s` # Current unix timestamp
	myname=""

	data=`${sqlitecmd} -list "${sqlite3db}" "SELECT calleridname,ts FROM callerid WHERE areacode=${areacode} AND phonenum=${phonenum};"`
	if [ "$?" != "0" -o "${data}" = "" ] ; then
		return 1
	fi

	while read x ; do
		ts=`echo ${x} | cut -d\| -f2`
		sql3_name=`echo ${x} | cut -d\| -f1`
		mydiff=`expr "${curr_ts}" - "${ts}"`
		if [ "${ts}" != "0" -a "${mydiff}" -gt "${cache_expire}" ] ; then
			${sqlitecmd} "${sqlite3db}" "DELETE FROM callerid WHERE areacode=${areacode} AND phonenum=${phonenum} AND ts=${ts};"
		else
			if [ "${sql3_name}" != "" ] ; then
				myname=${sql3_name}
			fi
		fi
	done <<< "`echo "${data}"`"

	if [ "${myname}" != "" ] ; then
		echo "${myname}"
	fi
}

lookup_mysql() {
	areacode=`get_areacode ${1}`
	phonenum=`get_phonenum ${1}`

	if [ "${areacode}" = "" -o "${phonenum}" = "" ] ; then
		return 1
	fi

	curr_ts=`date +%s` # Current unix timestamp
	myname=""

	data=`${mysqlcmd} -h ${mysqlhost} -u ${mysqluser} -p${mysqlpass} -e "SELECT calleridname,ts FROM ${mysqltable} WHERE areacode=${areacode} AND phonenum=${phonenum}\\G" ${mysqldb}`

	if [ "$?" != "0" -o "${data}" = "" ] ; then
		return 1
	fi

	while read x ; do
 		ts=`echo "${x}" | grep ts | awk '{ print $2 }'`
 		sql_name=`echo "${x}" | grep calleridname | sed -e 's,calleridname:\ \(.*\)$,\1,g'`

		#removed 1/2/2011: A. Nagel - was causing errors means no cache cleaning for now
		#mydiff=`expr "${curr_ts}" - "${ts}"`
		#if [ "${ts}" != "0" -a "${mydiff}" -gt "${cache_expire}" ] ; then
		#	${mysqlcmd} -h ${mysqlhost} -u ${mysqluser} -p${mysqlpass} -e "DELETE FROM ${mysqltable} WHERE perm=0 AND areacode=${areacode} AND phonenum=${phonenum} AND ts=${ts};" ${mysqldb}
		#fi

		if [ "${sql_name}" != "" ] ; then
			myname=${sql_name}
		fi
	done <<< "`echo "${data}"`"

	if [ "${myname}" != "" ] ; then
		echo "${myname}"
	fi
}

lookup_nanpa() {
	areacode=`get_areacode "${1}"`
	phonenum=`get_phonenum "${1}"`
	nxx=`get_nxx "${1}"`	
	line=`grep "${areacode}-${nxx}" "${nanpa_db}"`
	STATE=`echo "${line}" | cut -b 1-3 | sed 's/^[ ^t\x09]*//' | sed 's/[ ^t\x09]*$//'`
	RATECENTER=`echo "${line}" | cut -b 79-90 | sed 's/^[ ^t\x09]*//' | sed 's/[ ^t\x09]*$//'`
	if [ "${STATE}" != "" -a "${RATECENTER}" != "" ] ; then
		echo "${RATECENTER}, ${STATE}"
	fi
}

insert_sqlite3() {
	areacode=`get_areacode "${1}"`
	phonenum=`get_phonenum "${1}"`
	myname="${2}"
	mywho="${3}"

	if [ "${areacode}" = "" -o "${phonenum}" = "" -o "${myname}" = "" ] ; then
		return 1
	fi

	if [ ! -f "${sqlite3db}" ] ; then
		${sqlitecmd} "${sqlite3db}" 'CREATE TABLE callerid (areacode INT, phonenum INT, ts BIGINT, calleridname TEXT, origin TEXT);'
		if [ "$?" != "0" ] ; then
			return 1
		fi
		${sqlitecmd} "${sqlite3db}" 'CREATE INDEX areacode_idx ON callerid(areacode);'
		if [ "$?" != "0" ] ; then
			return 1
		fi
		${sqlitecmd} "${sqlite3db}" 'CREATE INDEX phonenum_idx ON callerid(phonenum);'
		if [ "$?" != "0" ] ; then
			return 1
		fi
		${sqlitecmd} "${sqlite3db}" 'CREATE INDEX ts_idx ON callerid(ts);'
		if [ "$?" != "0" ] ; then
			return 1
		fi
		chmod 666 "${sqlite3db}"
	else
		# Clear any previous entry that may exist
		${sqlitecmd} "${sqlite3db}" "DELETE FROM callerid WHERE areacode=${areacode} AND phonenum=${phonenum};" > /dev/null 2>&1
	fi

	if [ "${mywho}" = "manual" ] ; then
		curr_ts=0; # Non-expiring if manual
	else
		curr_ts=`date +%s`
	fi
	${sqlitecmd} "${sqlite3db}" "INSERT INTO callerid VALUES(${areacode}, ${phonenum}, ${curr_ts}, '${myname}', '${mywho}');"
	if [ "$?" != "0" ] ; then
		return 1
	fi
	return 0	
}

insert_mysql() {
	areacode=`get_areacode "${1}"`
	phonenum=`get_phonenum "${1}"`
	myname="${2}"
	mywho="${3}"

	if [ "${areacode}" = "" -o "${phonenum}" = "" -o "${myname}" = "" ] ; then
		return 1
	fi

	# Clear any previous entry that may exist
	${mysqlcmd} -h ${mysqlhost} -u ${mysqluser} -p${mysqlpass} -e "DELETE FROM ${mysqltable} WHERE areacode=${areacode} AND phonenum=${phonenum};" ${mysqldb} > /dev/null 2>&1

	if [ "${mywho}" = "manual" ] ; then
		curr_ts=0; # Non-expiring if manual
	else
		curr_ts=`date +%s`
	fi

	${mysqlcmd} -h ${mysqlhost} -u ${mysqluser} -p${mysqlpass} -e "INSERT INTO ${mysqltable} (areacode, phonenum, ts, calleridname, origin) VALUES (${areacode}, ${phonenum}, ${curr_ts}, '${myname}', '${mywho}');" ${mysqldb}

	if [ "$?" != "0" ] ; then
		return 1
	fi
	return 0	
}

lookup_name() {
	for x in ${1} ; do
		lu_name=""
		#echo "lookup via ${x}" 1>&2
		case ${x} in
			sqlite3)
				lu_name=`lookup_sqlite3 "${2}"`
			;;
			mysql)
				lu_name=`lookup_mysql "${2}"`
			;;
			411)
				lu_name=`lookup_411 "${2}"`
			;;
			#google)
			#	lu_name=`lookup_google "${2}"`
			#;;
			#anywho)
			#	lu_name=`lookup_anywho "${2}"`
			#;;
			nanpa)
				lu_name=`lookup_nanpa "${2}"`
			;;
			manual)
				lu_name="${3}"
			;;
			whitepages)
				lu_name=`lookup_whitepages "${2}"`
			;;
			#phonenumber)
			#	lu_name=`lookup_phonenumber "${2}"`
			#;;
		esac
		if [ "${lu_name}" != "" ] ; then
			if [ "${x}" != "sqlite3" -a "${cache_lookups}" = "1" ] ; then
				if [ "${x}" != "nanpa" -o "${cache_nanpa}" = "1" ] ; then
					insert_sqlite3 "${2}" "${lu_name}" "$x"
				fi
			fi

			if [ "${x}" != "mysql" -a "${cache_lookups}" = "2" ] ; then
				if [ "${x}" != "nanpa" -o "${cache_nanpa}" = "1" ] ; then
					insert_mysql "${2}" "${lu_name}" "$x"
				fi
			fi

			echo "${lu_name}"
			return 0
		fi 
	done
	return 0
}

if [ "$#" -lt "1" ] ; then
	echo "Usage: $0 <phonenum> <lookup order> <manual name>"
	echo ""
	echo "Phone Num  : phone number to look up"
	echo "Lookup List: 411 google anywho nanpa sqlite3"
	echo "    default: ${lookup_order}"
	echo "Manual Name: Enter if you want to add an entry to the sqlite3 cache"
	echo ""
	exit 1
fi

name=""
if [ "${1}" != "" ] ; then
	if [ "${2}" != "" ] ; then
		lookup_order="${2}"
	fi
	name=`lookup_name "${lookup_order}" "${1}" "${3}"`
fi

echo "${name}"
